﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DAL;
using System.Data.SqlClient;
using VOL;

namespace BLL
{
    public class ChiQuyBL
    {
        private DataAccess dataAccess;

        public ChiQuyBL()
        {
            dataAccess = new DataAccess();
        }
        /// <summary>
        /// 1. Hiển thị danh sách chi theo tháng và năm đưa vào
        /// </summary>
        /// <returns></returns>
        public DataTable GetData(int thangchi, int namchi)
        {
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("@THANGCHI", SqlDbType.Int) { Value = thangchi };
            param[1] = new SqlParameter("@NAMCHI", SqlDbType.Int) { Value = namchi };

            // cau query select
            string sql = "SELECT tblCHIQUY.IDCHI, tblCHIQUY.NOIDUNGCHI, tblDMCANBO.HOTEN, tblCHIQUY.SOTIENCHI, CONVERT(VARCHAR(10), tblCHIQUY.NTNCHI, 103) AS NTNCHI , tblCHIQUY.GHICHU " +
                         " FROM tblCHIQUY INNER JOIN tblDMCANBO ON tblCHIQUY.IDCANBOCHI = tblDMCANBO.IDCANBO " +
                         " WHERE  tblCHIQUY.THANGCHI = @THANGCHI AND tblCHIQUY.NAMCHI = @NAMCHI ";
            DataTable dt = dataAccess.GetData(sql, param);

            return dt;
        }

        /// <summary>
        /// 2. Thêm CHI
        /// </summary>
        /// <param name="obj">CHIQUY</param>
        /// <returns>true</returns>
        public bool InsertData(CHIQUY obj)
        {
            SqlParameter[] param = new SqlParameter[8];
            param[0] = new SqlParameter("@NoiDungChi", SqlDbType.NVarChar) { Value = obj.NoiDungChi };
            param[1] = new SqlParameter("@SoTienChi", SqlDbType.Float) { Value = obj.SoTienChi };
            param[2] = new SqlParameter("@IDCanBoChi", SqlDbType.Int) { Value = obj.IDCanBoChi };
            param[3] = new SqlParameter("@NgayChi", SqlDbType.Int) { Value = obj.NgayChi };
            param[4] = new SqlParameter("@ThangChi", SqlDbType.Int) { Value = obj.ThangChi };
            param[5] = new SqlParameter("@NamChi", SqlDbType.Int) { Value = obj.NamChi };
            param[6] = new SqlParameter("@NTNChi", SqlDbType.DateTime) { Value = obj.NTNChi };
            param[7] = new SqlParameter("@GhiChu", SqlDbType.NVarChar) { Value = obj.GhiChu };

            // cau query select
            string sql = "INSERT INTO tblCHIQUY (NOIDUNGCHI, SOTIENCHI, IDCANBOCHI, NGAYCHI, THANGCHI, NAMCHI, NTNCHI, GHICHU) VALUES (@NoiDungChi, @SoTienChi, @IDCanBoChi, @NgayChi, @ThangChi, @NamChi, @NTNChi,@GhiChu )";
            dataAccess.Execute(sql, param);

            return true;
        }

        /// <summary>
        /// 3. Xóa chi
        /// </summary>
        /// <param name="MaNguoiDung">IDCHI INT</param>
        /// <returns>true</returns>
        public bool DeleteData(int IDCHI)
        {
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@IDCHI", SqlDbType.Int) { Value = IDCHI };

            //LAY SO  TIEN CHI TRUOC DO
            float sumsotienchi;
            int namchi;

            string sql = "SELECT  NAMCHI FROM tblCHIQUY " +
                         " WHERE  IDCHI = @IDCHI ";
            DataTable dt = dataAccess.GetData(sql, param);

            namchi = int.Parse(dt.Rows[0]["NAMCHI"].ToString());

            // cau query select
            param[0] = new SqlParameter("@IDCHI", SqlDbType.Int) { Value = IDCHI };
            string sql1 = "DELETE FROM tblCHIQUY WHERE IDCHI = @IDCHI";
            dataAccess.Execute(sql1, param);

            //lấy tổng số tienf chi và cập nhật lại bảng quỹ phòng
            sumsotienchi = GetSumSotienChi(namchi);

            //cap nhat lại quy phong
            UpdateQuyPhong(namchi, sumsotienchi);

            return true;
        }

        //8. lay so tien thu tong
        public float GetSumSotienChi(int namchi)
        {
            // float SumSotienthu;
            string sql;
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@NAMCHI", SqlDbType.Int) { Value = namchi };

            // cau query select
            sql = "SELECT SUM(SOTIENCHI) AS SOTIENCHI, NAMCHI FROM tblCHIQUY WHERE (NAMCHI = @NAMCHI) GROUP BY NAMCHI ";

            DataTable dt = dataAccess.GetData(sql, param);

            if (dt.Rows.Count > 0)
            {
                return float.Parse(dt.Rows[0]["SOTIENCHI"].ToString());
            }
            else
                return 0;
        }


        ///4.1.CẬP NHẬT LẠI BẢNG QUY PHONG
        ///
        /// 
        
        public void UpdateQuyPhong (int nam, float sotienchi)
        {
            //cap nhật lại số tiền chi
            QuyPhongBL _quyPhongBL = new QuyPhongBL();
            QUYPHONG _objquyphong = new QUYPHONG();

            DataTable dtquyphong = _quyPhongBL.GetData(nam);

            _objquyphong.Nam = nam;
            _objquyphong.SoTienConLai = float.Parse(dtquyphong.Rows[0]["SOTIENNAMTRUOC"].ToString()) + float.Parse(dtquyphong.Rows[0]["SOTIENTHUKHAC"].ToString()) + float.Parse(dtquyphong.Rows[0]["SOTIENTHUCB"].ToString()) - sotienchi;
            _objquyphong.SoTienNamTruoc = float.Parse(dtquyphong.Rows[0]["SOTIENNAMTRUOC"].ToString());
            _objquyphong.SoTienThuKhac = float.Parse(dtquyphong.Rows[0]["SOTIENTHUKHAC"].ToString());
            _objquyphong.SoTienThuCB = float.Parse(dtquyphong.Rows[0]["SOTIENTHUCB"].ToString());

            _objquyphong.SoTienChi = sotienchi;

            _quyPhongBL.UpdateData(_objquyphong);
        }

        /// <summary>
        /// 4. sửa CHI
        /// </summary>
        /// <param name="obj">CHIQUY</param>
        /// <returns>true</returns>
        public bool UpdateData(CHIQUY obj)
        {
            SqlParameter[] param = new SqlParameter[9];
            param[0] = new SqlParameter("@NoiDungChi", SqlDbType.NVarChar) { Value = obj.NoiDungChi };
            param[1] = new SqlParameter("@SoTienChi", SqlDbType.Float) { Value = obj.SoTienChi };
            param[2] = new SqlParameter("@IDCanBoChi", SqlDbType.Int) { Value = obj.IDCanBoChi };
            param[3] = new SqlParameter("@NgayChi", SqlDbType.Int) { Value = obj.NgayChi };
            param[4] = new SqlParameter("@ThangChi", SqlDbType.Int) { Value = obj.ThangChi };
            param[5] = new SqlParameter("@NamChi", SqlDbType.Int) { Value = obj.NamChi };
            param[6] = new SqlParameter("@NTNChi", SqlDbType.DateTime) { Value = obj.NTNChi };
            param[7] = new SqlParameter("@GhiChu", SqlDbType.NVarChar) { Value = obj.GhiChu };

            param[8] = new SqlParameter("@IDCHI", SqlDbType.Int) { Value = obj.IDChi };
            // cau query select
            string sql = "UPDATE tblCHIQUY SET NOIDUNGCHI = @NoiDungChi, SOTIENCHI = @SoTienChi, IDCANBOCHI = @IDCanBoChi, NGAYCHI = @NgayChi, THANGCHI = @ThangChi, NAMCHI = @NamChi, NTNCHI = @NTNChi, GHICHU = @GhiChu " +
                           " WHERE IDCHI = @IDCHI";
            dataAccess.Execute(sql, param);

            return true;
        }

        /// <summary>
        /// 5. Tìm theo Mã chi
        /// </summary>
        /// <param name="IDTHU"></param>
        /// <returns></returns>
        public DataTable GetDataById(int IDCHI)
        {
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@IDCHI", SqlDbType.NVarChar) { Value = IDCHI };
            // cau query select
            string sql = "SELECT * FROM tblCHIQUY WHERE IDCHI = @IDCHI";
            DataTable dt = dataAccess.GetData(sql, param);

            return dt;
        }

        /// <summary>
        /// 5.1. Tìm theo Mã CHI
        /// </summary>
        /// <param name="THUQUY"></param>
        /// <returns></returns>
        //Tìm
        public DataTable searchIDCHI(CHIQUY obj)
        {
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@IDCHI", SqlDbType.Int) { Value = obj.IDChi };

            string strSql = "SELECT * FROM tblCHIQUY WHERE IDCHI = @IDCHI";
            return dataAccess.GetData(strSql, param);
        }

    }
}
